{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/Traveling_tourist/Part1%20-%20Import%20knowledge%20graph.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "P5pgH9OJ3gXU"
   },
   "source": [
    "* Link to original article: https://towardsdatascience.com/traveling-tourist-part-1-import-wikidata-to-neo4j-with-neosemantics-library-f80235f40dc5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "bGKdfmzo2IwW",
    "outputId": "ad95dc5a-a37b-44d6-e405-a2ce9b0e6d68"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting neo4j\n",
      "  Downloading neo4j-4.4.2.tar.gz (89 kB)\n",
      "\u001b[?25l\r",
      "\u001b[K     |███▋                            | 10 kB 22.2 MB/s eta 0:00:01\r",
      "\u001b[K     |███████▎                        | 20 kB 14.5 MB/s eta 0:00:01\r",
      "\u001b[K     |███████████                     | 30 kB 9.6 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████▋                 | 40 kB 3.8 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████████▎             | 51 kB 3.8 MB/s eta 0:00:01\r",
      "\u001b[K     |██████████████████████          | 61 kB 4.5 MB/s eta 0:00:01\r",
      "\u001b[K     |█████████████████████████▋      | 71 kB 4.8 MB/s eta 0:00:01\r",
      "\u001b[K     |█████████████████████████████▎  | 81 kB 5.0 MB/s eta 0:00:01\r",
      "\u001b[K     |████████████████████████████████| 89 kB 3.4 MB/s \n",
      "\u001b[?25hRequirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from neo4j) (2018.9)\n",
      "Building wheels for collected packages: neo4j\n",
      "  Building wheel for neo4j (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
      "  Created wheel for neo4j: filename=neo4j-4.4.2-py3-none-any.whl size=115365 sha256=c1a760067e49d1346f01e245b6e72ef89884d7e0b7aaac3a7f8ac28bbf320889\n",
      "  Stored in directory: /root/.cache/pip/wheels/10/d6/28/95029d7f69690dbc3b93e4933197357987de34fbd44b50a0e4\n",
      "Successfully built neo4j\n",
      "Installing collected packages: neo4j\n",
      "Successfully installed neo4j-4.4.2\n"
     ]
    }
   ],
   "source": [
    "!pip install neo4j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "WDqJyfMe19tC"
   },
   "outputs": [],
   "source": [
    "# Define Neo4j connections\n",
    "import pandas as pd\n",
    "from neo4j import GraphDatabase\n",
    "host = 'bolt://3.231.25.240:7687'\n",
    "user = 'neo4j'\n",
    "password = 'hatchets-visitor-axes'\n",
    "driver = GraphDatabase.driver(host,auth=(user, password))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "mcP7Kh-_19tF"
   },
   "outputs": [],
   "source": [
    "def run_query(query):\n",
    "    with driver.session() as session:\n",
    "        result = session.run(query)\n",
    "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0Aiq-57O19tG"
   },
   "source": [
    "## Agenda\n",
    "- Install Neosemantics library\n",
    "- Graph model\n",
    "- Construct WikiData SPARQL query\n",
    "- Import RDF Graph\n",
    "- Reverse Geocode with OSM API\n",
    "- Verify Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fLcfqZ4D19tJ"
   },
   "source": [
    "### Install Neosemantics library\n",
    "In this blog series, we will use the standard APOC and GDS libraries, which we can install with a single click in the Neo4j Desktop application. On top of that, we will add the Neosemantics library to our stack. It is used to interact with RDF data in the Neo4j environment. We can either import RDF data to Neo4j or export property graph model in RDF format.\n",
    "To install the Neosemantics library, we download the latest release and save it to the Neo4j plugins folder. We also need to add the following line to the Neo4j configuration file.\n",
    "\n",
    "<code>dbms.unmanaged_extension_classes=n10s.endpoint=/rdf</code>\n",
    "\n",
    "We are now ready to start our Neo4j instance. First, we need to initiate the Neosemantics configuration with the following cypher procedure.\n",
    "\n",
    "<code>CALL n10s.graphconfig.init({handleVocabUris: \"IGNORE\"})</code>\n",
    "\n",
    "Take a look at the documentation for information about the configuration options."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NWXlA4Vr19tJ"
   },
   "source": [
    "Graph model\n",
    "Monuments are in the center of our graph. We have their name and the URL of the image stored as a node property. The monuments have been influenced by various architectural styles, which we indicate as a relationship to an Architecture node. We will save the city and the state of the monument as a two-level hierarchical location tree.\n",
    "Graph model created with draw.ioThe Neosemantics library requires a unique constraint on the property \"uri\" of the nodes labeled Resource. We will also add indexes for the State and City nodes. The <code>apoc.schema.assert</code> procedure allows us to define many indexes and unique constraints with a single call."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 520
    },
    "id": "dtDdZP8m19tK",
    "outputId": "b909ab16-30f0-4788-9879-5e4f14d8b4ac"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>param</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>handleVocabUris</td>\n",
       "      <td>IGNORE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>handleMultival</td>\n",
       "      <td>OVERWRITE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>handleRDFTypes</td>\n",
       "      <td>LABELS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>keepLangTag</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>keepCustomDataTypes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>applyNeo4jNaming</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>baseSchemaNamespace</td>\n",
       "      <td>neo4j://graph.schema#</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>baseSchemaPrefix</td>\n",
       "      <td>n4sch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>classLabel</td>\n",
       "      <td>Class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>subClassOfRel</td>\n",
       "      <td>SCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>dataTypePropertyLabel</td>\n",
       "      <td>Property</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>objectPropertyLabel</td>\n",
       "      <td>Relationship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>subPropertyOfRel</td>\n",
       "      <td>SPO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>domainRel</td>\n",
       "      <td>DOMAIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>rangeRel</td>\n",
       "      <td>RANGE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    param                  value\n",
       "0         handleVocabUris                 IGNORE\n",
       "1          handleMultival              OVERWRITE\n",
       "2          handleRDFTypes                 LABELS\n",
       "3             keepLangTag                  False\n",
       "4     keepCustomDataTypes                  False\n",
       "5        applyNeo4jNaming                  False\n",
       "6     baseSchemaNamespace  neo4j://graph.schema#\n",
       "7        baseSchemaPrefix                  n4sch\n",
       "8              classLabel                  Class\n",
       "9           subClassOfRel                    SCO\n",
       "10  dataTypePropertyLabel               Property\n",
       "11    objectPropertyLabel           Relationship\n",
       "12       subPropertyOfRel                    SPO\n",
       "13              domainRel                 DOMAIN\n",
       "14               rangeRel                  RANGE"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n10s_config_query = \"\"\"\n",
    "\n",
    "CALL n10s.graphconfig.init({handleVocabUris: \"IGNORE\"})\n",
    "\"\"\"\n",
    "run_query(n10s_config_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "zmaV2Q5U19tL",
    "outputId": "f40bf17b-a6c5-4847-ddfa-4d76d06717c7"
   },
   "outputs": [],
   "source": [
    "schema_assert_query = \"\"\"\n",
    "\n",
    "CALL apoc.schema.assert(\n",
    "  {State:['id'], City:['id']},\n",
    "  {Resource:['uri']})\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(schema_assert_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "JFyCRiym19tM"
   },
   "source": [
    "### Construct WikiData SPARQL query\n",
    "\n",
    "For me, the easiest way to construct a new SPARQL query is using the WikiData query editor. It has a lovely autocomplete feature. It also helps with query debugging.We want to retrieve all the instances of monuments located in Spain. I have found the easiest way to find various entities on WikiData is by simply using Google search. You can then inspect all the available properties of the entity on the website.\n",
    "The first two lines in the WHERE clause define the entities we are looking for:\n",
    "<pre>\n",
    "// Entity is an instance of monument entity\n",
    "?item wdt:P31 wd:Q4989906 . \n",
    "// Entity is located in Spain\n",
    "?item wdt:P17 wd:Q29 .\n",
    "</pre>\n",
    "Next, we also determine which properties of the entities we are interested in. In our case, we would like to retrieve the monument's name, image, location, and architectural style. If we run this query in the query editor, we get the following results.\n",
    "\n",
    "Now we can go ahead and import the graph to Neo4j."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "xUgC3Ndp19tN",
    "outputId": "db41a3c3-b4dd-4928-9937-c3356e9e390e"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>terminationStatus</th>\n",
       "      <th>triplesLoaded</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OK</td>\n",
       "      <td>6456</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  terminationStatus  triplesLoaded\n",
       "0                OK           6456"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_query = \"\"\"\n",
    "\n",
    "WITH 'PREFIX sch: <http://schema.org/> \n",
    "CONSTRUCT{ ?item a sch:Monument; \n",
    "            sch:name ?monumentName; \n",
    "            sch:location ?location; \n",
    "            sch:img ?imageAsStr; \n",
    "            sch:ARCHITECTURE ?architecture. \n",
    "           ?architecture a sch:Architecture; \n",
    "            sch:name ?architectureName } \n",
    "WHERE { ?item wdt:P31 wd:Q4989906 . \n",
    "        ?item wdt:P17 wd:Q29 . \n",
    "        ?item rdfs:label ?monumentName . \n",
    "         filter(lang(?monumentName) = \"en\") \n",
    "        ?item wdt:P625 ?location . \n",
    "        ?item wdt:P149 ?architecture . \n",
    "        ?architecture rdfs:label ?architectureName .\n",
    "         filter(lang(?architectureName) = \"en\") \n",
    "        ?item wdt:P18 ?image . \n",
    "         bind(str(?image) as ?imageAsStr) }' AS sparql \n",
    "CALL n10s.rdf.import.fetch(\n",
    "  \"https://query.wikidata.org/sparql?query=\" +   \n",
    "   apoc.text.urlencode(sparql),\"JSON-LD\", \n",
    "   { headerParams: { Accept: \"application/ld+json\"} , \n",
    "     handleVocabUris: \"IGNORE\"}) \n",
    "YIELD terminationStatus, triplesLoaded\n",
    "RETURN terminationStatus, triplesLoaded\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(import_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Z8ahbYrU19tO"
   },
   "source": [
    "Let's start with some exploratory graph queries. We will first count the number of monuments in our graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "7LrAI3Xv19tO",
    "outputId": "69de350b-0d7a-404e-88f4-9644c3d06e90"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1198</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(*)\n",
       "0      1198"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monument_count_query = \"\"\"\n",
    "\n",
    "MATCH (n:Monument) \n",
    "RETURN count(*)\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(monument_count_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ExHnqE_A19tP"
   },
   "source": [
    "We have imported 1299 monuments into our graph. We will continue with counting the number of monuments grouped by an architectural style."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "aKwLDKjt19tQ",
    "outputId": "1b75e6a6-e51f-4300-ea06-a5e52a33fc9e"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>monument</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gothic architecture</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baroque architecture</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Romanesque architecture</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Renaissance architecture</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baroque</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   monument  count\n",
       "0       Gothic architecture    207\n",
       "1      baroque architecture    192\n",
       "2   Romanesque architecture    156\n",
       "3  Renaissance architecture    144\n",
       "4                   Baroque     60"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monument_count_style_query = \"\"\"\n",
    "\n",
    "MATCH (n:Architecture) \n",
    "RETURN n.name as monument, \n",
    "       count{ (n)<--() } as count \n",
    "ORDER BY count DESC \n",
    "LIMIT 5\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(monument_count_style_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "oQpP5bq119tQ",
    "outputId": "16e34117-c9e3-4a71-8334-51ffb9398918"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>terminationStatus</th>\n",
       "      <th>triplesLoaded</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OK</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OK</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>OK</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>OK</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>OK</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>OK</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>OK</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   terminationStatus  triplesLoaded\n",
       "0                 OK              4\n",
       "1                 OK              0\n",
       "2                 OK              4\n",
       "3                 OK              0\n",
       "4                 OK              0\n",
       "..               ...            ...\n",
       "93                OK              4\n",
       "94                OK              0\n",
       "95                OK              7\n",
       "96                OK              4\n",
       "97                OK              7\n",
       "\n",
       "[98 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "architecture_subclass_import = \"\"\"\n",
    "\n",
    "MATCH (a:Architecture) \n",
    "WITH ' PREFIX sch: <http://schema.org/> \n",
    "CONSTRUCT { ?item a sch:Architecture; \n",
    "             sch:SUBCLASS_OF ?style. \n",
    "            ?style a sch:Architecture; \n",
    "             sch:name ?styleName;} \n",
    "WHERE { filter (?item = <' + a.uri + '>) \n",
    "        ?item wdt:P279 ?style . \n",
    "        ?style rdfs:label ?styleName \n",
    "         filter(lang(?styleName) = \"en\") } ' AS sparql \n",
    "CALL n10s.rdf.import.fetch(\n",
    "  \"https://query.wikidata.org/sparql?query=\" + \n",
    "    apoc.text.urlencode(sparql),\"JSON-LD\", \n",
    "  { headerParams: { Accept: \"application/ld+json\"} , \n",
    "    handleVocabUris: \"IGNORE\"}) \n",
    "YIELD terminationStatus, triplesLoaded \n",
    "RETURN terminationStatus, triplesLoaded\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(architecture_subclass_import)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "0ViMI-uA19tR",
    "outputId": "6c13a79c-b41f-49a9-9fb6-9f4dc2d8f0ac"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>child_architecture</th>\n",
       "      <th>parent_architecture</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pre-Romanesque art</td>\n",
       "      <td>medieval art</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Art Nouveau</td>\n",
       "      <td>decorative arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gothic art</td>\n",
       "      <td>medieval art</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catalan modernism</td>\n",
       "      <td>Art Nouveau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Catalan modernism</td>\n",
       "      <td>modernism</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   child_architecture parent_architecture\n",
       "0  pre-Romanesque art        medieval art\n",
       "1         Art Nouveau     decorative arts\n",
       "2          Gothic art        medieval art\n",
       "3   Catalan modernism         Art Nouveau\n",
       "4   Catalan modernism           modernism"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "architecture_hierarchy_query = \"\"\"\n",
    "\n",
    "MATCH (a:Architecture)-[:SUBCLASS_OF]->(b:Architecture)\n",
    "RETURN a.name as child_architecture,\n",
    "       b.name as parent_architecture\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "\n",
    "run_query(architecture_hierarchy_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-GKpgO5y19tR"
   },
   "source": [
    "It seems that modernism is a child category of Art Noveau, and Art Noveau is a child category of decorative arts.\n",
    "### Spatial enrichment\n",
    "At first, I wanted to include the municipality information of monuments available on WikiData, but as it turned out, this information is relatively sparse. No worries though, I later realized we could use the reverse geocode API to retrieve this information. APOC has a dedicated procedure available for reverse geocoding. By default, it uses Open Street Map API, but we can customize it to work with other providers as well. Check the documentation for more information.\n",
    "First, we have to transform the location information to a spatial point data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "EF4Smqas19tS",
    "outputId": "7fdfa09b-f7c6-45ff-c679-ae9a4bc3d313"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transform_location_point_query = \"\"\"\n",
    "\n",
    "MATCH (m:Monument) \n",
    "WITH m, \n",
    "   m.location.y AS latitude,\n",
    "   m.location.x AS longitude\n",
    "SET m.location_point = point(\n",
    "  {latitude: latitude, \n",
    "   longitude: longitude})\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(transform_location_point_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0Mrwi04c19tS"
   },
   "source": [
    "Check a sample response from the OSM reverse GeoCode API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "XTeWv9AQ19tS",
    "outputId": "e2ca8f71-1d48-4b5c-9fb6-8ae04c80d846"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'country': 'España', 'country_code': 'es', 'h...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                data\n",
       "0  {'country': 'España', 'country_code': 'es', 'h..."
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "example_osm_response = \"\"\"\n",
    "\n",
    "MATCH (m:Monument)\n",
    "WITH m LIMIT 1\n",
    "CALL apoc.spatial.reverseGeocode(\n",
    "  m.location_point.latitude,\n",
    "  m.location_point.longitude)\n",
    "YIELD data\n",
    "RETURN data\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(example_osm_response)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "k5NjZaP019tT"
   },
   "source": [
    "Open Street Map API is a tad interesting as it differs between cities, towns, and villages. Also, the monuments located in the Canaries have no state available but are a part of the Canaries archipelago. We will treat archipelago as a state and lump city, town, and village under a single label City. For batching purposes, we will use the <code>apoc.periodic.iterate</code> procedure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "eCcdc_1M19tT",
    "outputId": "58164128-28b6-4a53-daeb-b9bcdc63587a"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_spatial_query = \"\"\"\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"https://raw.githubusercontent.com/tomasonjo/blogs/master/Traveling_tourist/traveling_tourist_cities.csv\" as row\n",
    "MATCH (m:Monument{uri:row.uri})\n",
    "MERGE (c:City{id:row.city})\n",
    "MERGE (s:State{id:row.state})\n",
    "MERGE (m)-[:IS_IN]->(c)\n",
    "MERGE (c)-[:IS_IN]->(s);\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(import_spatial_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 112
    },
    "id": "SzbevFgI19tU",
    "outputId": "d76b7d9b-0aeb-44cd-eb5e-5e77c0146ff8"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>has_location</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>True</td>\n",
       "      <td>1013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   has_location  count\n",
       "0         False    185\n",
       "1          True   1013"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location_exists_check = \"\"\"\n",
    "\n",
    "MATCH (m:Monument) \n",
    "RETURN exists { (m)-[:IS_IN]->() } as has_location, \n",
    "       count(*) as count\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(location_exists_check)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "fJZz2anw19tU",
    "outputId": "181046aa-34c0-4d1b-8adc-c599182d2759"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>architecture</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>vernacular architecture</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Romanesque architecture</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Art Nouveau</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Catalan modernism</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gothic architecture</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              architecture  count\n",
       "0  vernacular architecture     32\n",
       "1  Romanesque architecture     28\n",
       "2              Art Nouveau     22\n",
       "3        Catalan modernism     16\n",
       "4      Gothic architecture     15"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "catalunya_monument_style = \"\"\"\n",
    "\n",
    "MATCH (s:State{id:'Catalunya'})<-[:IS_IN*2..2]-(:Monument)-[:ARCHITECTURE]->(architecture)\n",
    "RETURN architecture.name as architecture,\n",
    "       count(*) as count\n",
    "ORDER BY count DESC\n",
    "LIMIT 5\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(catalunya_monument_style)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "CcLnxdJC19tU",
    "outputId": "29e38169-e2a7-474d-e762-c65084b7db6d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>architecture</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Euskadi</td>\n",
       "      <td>baroque architecture</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Castilla y León</td>\n",
       "      <td>Romanesque architecture</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Catalunya</td>\n",
       "      <td>vernacular architecture</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andalucía</td>\n",
       "      <td>Gothic architecture</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comunitat Valenciana</td>\n",
       "      <td>medieval architecture</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  state             architecture  count\n",
       "0               Euskadi     baroque architecture     59\n",
       "1       Castilla y León  Romanesque architecture     45\n",
       "2             Catalunya  vernacular architecture     32\n",
       "3             Andalucía      Gothic architecture     29\n",
       "4  Comunitat Valenciana    medieval architecture     27"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_monument_style = \"\"\"\n",
    "\n",
    "MATCH (s:State)\n",
    "CALL {\n",
    "  WITH s\n",
    "  MATCH (s)<-[:IS_IN*2..2]-()-[:ARCHITECTURE]->(a)\n",
    "  RETURN a.name as architecture, count(*) as count\n",
    "  ORDER BY count DESC LIMIT 1\n",
    "}\n",
    "RETURN s.id as state, architecture, count\n",
    "ORDER BY count DESC \n",
    "LIMIT 5\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(states_monument_style)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-gdmZxqu19tV"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "name": "Part1 - Import knowledge graph.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
